// Licensed to the Apache Software Foundation (ASF) under one
// or more contributor license agreements.  See the NOTICE file
// distributed with this work for additional information
// regarding copyright ownership.  The ASF licenses this file
// to you under the Apache License, Version 2.0 (the
// "License"); you may not use this file except in compliance
// with the License.  You may obtain a copy of the License at
//
//   http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing,
// software distributed under the License is distributed on an
// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
// KIND, either express or implied.  See the License for the
// specific language governing permissions and limitations
// under the License.

suite("test_date_trunc_prune") {
    sql "SET enable_nereids_planner=true"
    sql "SET enable_fallback_to_original_planner=false"
    sql "drop table if exists mal_test_partition_range5"
    sql"""
        CREATE TABLE `mal_test_partition_range5` (
          `a` INT NULL,
          `b` datetime not NULL,
          `c` INT NULL
        ) ENGINE=OLAP
        DUPLICATE KEY(`a`, `b`, `c`)
        PARTITION BY RANGE(`b`)
        (PARTITION p1 VALUES [("2020-01-05 10:00:00"), ("2020-01-09 10:00:00")),
        PARTITION p2 VALUES [("2020-01-09 10:00:00"), ("2020-01-13 10:00:00")),
        PARTITION p3 VALUES [("2020-01-13 10:00:00"), ("2020-01-19 10:00:00")))
        DISTRIBUTED BY HASH(`a`) BUCKETS 10
        PROPERTIES (
        "replication_allocation" = "tag.location.default: 1"
        );"""
    sql """insert into mal_test_partition_range5 values(1,"2020-01-09 09:00:00",4),(1,"2020-01-09 11:00:00",4),
        (1,"2020-01-13 11:00:00",4),(1,"2020-01-13 09:00:00",4)"""
    // > >= < <= = <=>
    explain {
        sql """select * from mal_test_partition_range5 where date_trunc(b,'day')<="2020-01-08" """
        contains("partitions=1/3 (p1)")
    }
    explain {
        sql """select * from mal_test_partition_range5 where date_trunc(b,'day')<"2020-01-08" """
        contains("partitions=1/3 (p1)")
    }
    explain {
        sql """select * from mal_test_partition_range5 where "2020-01-08">=date_trunc(b,'day')"""
        contains("partitions=1/3 (p1)")
    }
    explain {
        sql """select * from mal_test_partition_range5 where "2020-01-08" > date_trunc(b,'day')"""
        contains("partitions=1/3 (p1)")
    }
    explain {
        sql """select * from mal_test_partition_range5 where date_trunc(b,'day')="2020-01-08" """
        contains("partitions=1/3 (p1)")
    }
    explain {
        sql """select * from mal_test_partition_range5 where "2020-01-08" = date_trunc(b,'day')"""
        contains("partitions=1/3 (p1)")
    }
    explain {
        sql """select * from mal_test_partition_range5 where date_trunc(b,'day')>="2020-01-14" """
        contains("partitions=1/3 (p3)")
    }
    explain {
        sql """select * from mal_test_partition_range5 where date_trunc(b,'day')>"2020-01-14" """
        contains("partitions=1/3 (p3)")
    }
    explain {
        sql """select * from mal_test_partition_range5 where "2020-01-14"<=date_trunc(b,'day')"""
        contains("partitions=1/3 (p3)")
    }
    explain {
        sql """select * from mal_test_partition_range5 where "2020-01-14" < date_trunc(b,'day')"""
        contains("partitions=1/3 (p3)")
    }

    explain {
        sql """select * from mal_test_partition_range5 where  date_trunc(b,'day') in ("2020-01-13 00:00:00")"""
        contains("partitions=2/3 (p2,p3)")
    }
    explain {
        sql """select * from mal_test_partition_range5 where "2020-01-14" <=> date_trunc(b,'day')"""
        contains("partitions=1/3 (p3)")
    }
    explain {
        sql """select * from mal_test_partition_range5 where  date_trunc(b,'day') <=>"2020-01-14" """
        contains("partitions=1/3 (p3)")
    }

    // and or
    explain {
        sql """select * from mal_test_partition_range5 where date_trunc(b,'day')>"2020-01-09"  and date_trunc(b,'day') <"2020-01-13" """
        contains("partitions=1/3 (p2)")
    }
    explain {
        sql """select * from mal_test_partition_range5 where date_trunc(b,'day')>"2020-01-09"  or date_trunc(b,'day') <"2020-01-13" """
        contains("partitions=3/3 (p1,p2,p3)")
    }
    explain {
        sql """select * from mal_test_partition_range5 where date_trunc(b,'day')>"2020-01-14"  or date_trunc(b,'day') <"2020-01-06" """
        contains("partitions=2/3 (p1,p3)")
    }

    explain {
        sql """select * from mal_test_partition_range5 where date_trunc(b,'day') between "2020-01-09"  and "2020-01-13" """
        contains("partitions=3/3 (p1,p2,p3)")

    }
    explain {
        sql """select * from mal_test_partition_range5 where date_trunc(b,'day') between "2020-01-10"  and "2020-01-14" """
        contains("partitions=2/3 (p2,p3)")

    }
    explain {
        sql """select * from mal_test_partition_range5 where date_trunc(b,'day') between "2020-01-10"  and "2020-01-12" """
        contains("partitions=1/3 (p2)")
    }

    // test not
    for (int i = 0; i < 2; i++) {
        if (i == 0) {
            // forbid rewrite not a>1 to a<=1
            sql "set disable_nereids_rules = 'REWRITE_FILTER_EXPRESSION'"
        } else {
            sql "set disable_nereids_rules = ''"
        }
        explain {
            sql """select * from mal_test_partition_range5 where not date_trunc(b,'day')<="2020-01-14" """
            contains("partitions=1/3 (p3)")
        }
        explain {
            sql """ select * from mal_test_partition_range5 where not date_trunc(b,'day')<"2020-01-14" """
            contains("partitions=1/3 (p3)")
        }
        explain {
            sql """select * from mal_test_partition_range5 where not "2020-01-14">=date_trunc(b,'day')"""
            contains("partitions=1/3 (p3)")
        }
        explain {
            sql """select * from mal_test_partition_range5 where not "2020-01-14" > date_trunc(b,'day')"""
            contains("partitions=1/3 (p3)")
        }
        explain {
            sql """select * from mal_test_partition_range5 where not date_trunc(b,'day')="2020-01-08" """
            contains("partitions=3/3 (p1,p2,p3)")
        }
        explain {
            sql """select * from mal_test_partition_range5 where not "2020-01-08" = date_trunc(b,'day')"""
            contains("partitions=3/3 (p1,p2,p3)")
        }
        explain {
            sql """select * from mal_test_partition_range5 where not date_trunc(b,'day')>="2020-01-9" """
            contains("partitions=1/3 (p1)")
        }
        explain {
            sql """select * from mal_test_partition_range5 where not date_trunc(b,'day')>"2020-01-9" """
            contains("partitions=2/3 (p1,p2)")
        }
        explain {
            sql """select * from mal_test_partition_range5 where not "2020-01-9"<=date_trunc(b,'day')"""
            contains("partitions=1/3 (p1)")
        }
        explain {
            sql """select * from mal_test_partition_range5 where not "2020-01-9" < date_trunc(b,'day')"""
            contains("partitions=2/3 (p1,p2)")
        }
        explain {
            sql """ select * from mal_test_partition_range5 where  not date_trunc(b,'day') in ("2020-01-13 00:00:00")"""
            contains("partitions=3/3 (p1,p2,p3)")
        }
        explain {
            sql """ select * from mal_test_partition_range5 where not "2020-01-14" <=> date_trunc(b,'day')"""
            contains("partitions=3/3 (p1,p2,p3)")
        }
        explain {
            sql """select * from mal_test_partition_range5 where not date_trunc(b,'day') <=>"2020-01-14" """
            contains("partitions=3/3 (p1,p2,p3)")
        }
        explain {
            sql """ select * from mal_test_partition_range5 where not  (date_trunc(b,'day')>"2020-01-09"  and date_trunc(b,'day') <"2020-01-13") """
            contains("partitions=3/3 (p1,p2,p3)")
        }
        explain {
            sql """ select * from mal_test_partition_range5 where not (date_trunc(b,'day')>="2020-01-13"  or date_trunc(b,'day') <="2020-01-9") """
            contains("partitions=1/3 (p2)")
        }
        explain {
            sql """ select * from mal_test_partition_range5 where not date_trunc(b,'day')<="2020-01-14"  or date_trunc(b,'day') <"2020-01-06" """
            contains("partitions=2/3 (p1,p3)")
        }
        explain {
            sql """select * from mal_test_partition_range5 where not date_trunc(b,'day') between "2020-01-09"  and "2020-01-13" """
            contains("partitions=2/3 (p1,p3)")
        }
        explain {
            sql """select * from mal_test_partition_range5 where not date_trunc(b,'day') between "2020-01-10"  and "2020-01-14" """
            contains("partitions=3/3 (p1,p2,p3)")
        }
        explain {
            sql """select * from mal_test_partition_range5 where date_trunc(b,'day') not between "2020-01-4"  and "2020-01-15" """
            contains("partitions=1/3 (p3)")
        }
    }

    // trunc(b) and b
    explain {
        sql """select * from mal_test_partition_range5 where (not date_trunc(b,'day')<="2020-01-14") or b<"2020-01-9" """
        contains("partitions=2/3 (p1,p3)")
    }
    explain {
        sql """select * from mal_test_partition_range5 where  date_trunc(b,'day')<"2020-01-13" and  b>"2020-01-10" """
        contains("partitions=1/3 (p2)")
    }
    explain {
        sql """ select * from mal_test_partition_range5 where  date_trunc(b,'day')<"2020-01-13" and  b>"2020-01-9" """
        contains("partitions=2/3 (p1,p2)")
    }
    explain {
        sql """ select * from mal_test_partition_range5 where ( date_trunc(b,'day')<="2020-01-14" and b >"2020-01-12") or b<"2020-01-9" """
        contains("partitions=3/3 (p1,p2,p3)")
    }
    explain {
        sql """ select * from mal_test_partition_range5 where ( date_trunc(b,'day')<="2020-01-14" and b >"2020-01-19") or b<"2020-01-9" """
        contains("partitions=2/3 (p1,p3)")
    }
    explain {
        sql """ select * from mal_test_partition_range5 where ( date_trunc(b,'day')<="2020-01-14" and b >"2020-01-20") or b<"2020-01-9" """
        contains("partitions=1/3 (p1)")
    }
    explain {
        sql """ select * from mal_test_partition_range5 where ( not date_trunc(b,'day')<="2020-01-14" or b >"2020-01-20") or b<"2020-01-9" """
        contains("partitions=2/3 (p1,p3)")
    }
    explain {
        sql """ select * from mal_test_partition_range5 where (  date_trunc(b,'day') between "2020-01-14" and "2020-01-20") or b<"2020-01-9" """
        contains("partitions=2/3 (p1,p3)")
    }

    // is null, can support but now not
    sql "drop table if exists null_range_date"
    sql """
        create table null_range_date(
        k0 datetime null
        )
        partition by range (k0)
        (
        PARTITION p10 values less than ('2022-01-01 10:00:00'),
        PARTITION p100 values less than ('2022-01-04 10:00:00'),
        PARTITION pMAX values less than (maxvalue)
        )
        DISTRIBUTED BY HASH(`k0`) BUCKETS 1 properties("replication_num"="1")
    """
    sql "insert into null_range_date values('2022-01-03 10:00:00'),('2019-01-01 10:00:00'),('2022-01-02 10:00:00'),('2024-01-01 10:00:00'),(null);"
    explain {
        sql "select * from null_range_date where date_trunc(k0,'day') is null"
        contains("partitions=3/3 (p10,p100,pMAX)")
    }
    // test infinite range
    explain {
        sql "select * from null_range_date where date_trunc(k0,'day') <'2022-1-3'"
        contains("partitions=2/3 (p10,p100)")
    }
    explain {
        sql "select * from null_range_date where date_trunc(k0,'day') >'2022-1-3'"
        contains("partitions=2/3 (p100,pMAX)")
    }

    sql "drop table if exists mal_test_partition_range2_two_date_int"
    sql """CREATE TABLE `mal_test_partition_range2_two_date_int` (
          `dt` DATETIME NULL,
          `id` INT NULL,
          `c` INT NULL
        ) ENGINE=OLAP
        DUPLICATE KEY(`dt`, `id`, `c`)
        PARTITION BY RANGE(`dt`, `id`)
        (PARTITION p201701_1000 VALUES [('0000-01-01', "-2147483648"), ('2017-02-01', "1000")),
        PARTITION p201702_2000 VALUES [('2017-02-01', "1000"), ('2017-03-01', "2000")),
        PARTITION p201703_all VALUES [('2017-03-01', "2000"), ('2017-04-01', "-2147483648")))
        DISTRIBUTED BY HASH(`dt`) BUCKETS 10
        PROPERTIES (
        "replication_allocation" = "tag.location.default: 1"
        );"""
    sql """insert into mal_test_partition_range2_two_date_int values('2017-01-03 10:00:00', 3,23),('2017-02-04 10:00:00', 333,4),('2017-03-05 10:00:00', 1222,6);"""
    explain {
        sql """select * from mal_test_partition_range2_two_date_int where date_trunc(dt,'month') = '2017-2-1 00:00:00' and id>0 ;"""
        contains ("partitions=2/3 (p201701_1000,p201702_2000)")
    }
    explain {
        sql "select * from mal_test_partition_range2_two_date_int where date_trunc(dt,'month') > '2017-2-1  00:00:00' and id>100;"
        contains("partitions=2/3 (p201702_2000,p201703_all)")
    }

    sql "drop table if exists test_partition_prune_or;"
    sql """create table test_partition_prune_or (
    col_int_undef_signed int   ,
    col_date_undef_signed date   ,
    pk int,
    col_int_undef_signed2 int   ,
    col_date_undef_signed2 date   ,
    col_varchar_10__undef_signed varchar(10)   ,
    col_varchar_1024__undef_signed varchar(1024)   
    ) engine=olap
    DUPLICATE KEY(col_int_undef_signed, col_date_undef_signed, pk)
    PARTITION BY             RANGE(col_int_undef_signed, col_date_undef_signed) (
                    PARTITION p0 VALUES LESS THAN ('4', '2023-12-11'),
                    PARTITION p1 VALUES LESS THAN ('6', '2023-12-15'),
                    PARTITION p2 VALUES LESS THAN ('7', '2023-12-16'),
                    PARTITION p3 VALUES LESS THAN ('8', '2023-12-25'),
                    PARTITION p4 VALUES LESS THAN ('8', '2024-01-18'),
                    PARTITION p5 VALUES LESS THAN ('10', '2024-02-18'),
                    PARTITION p6 VALUES LESS THAN ('1147483647', '2056-12-31'),
                    PARTITION p100 VALUES LESS THAN ('2147483647', '9999-12-31')
                )
            
    distributed by hash(pk) buckets 10
    properties("replication_num" = "1");"""

    sql """insert into test_partition_prune_or(pk,col_int_undef_signed,col_int_undef_signed2,col_date_undef_signed,col_date_undef_signed2,col_varchar_10__undef_signed,col_varchar_1024__undef_signed) 
    values (0,9,8,'2026-02-18','2027-01-09',null,null),
    (1,6,6,'2023-12-10','2023-12-14','v','x'),(2,null,1,'2025-06-18','2027-01-16','o','m'),(3,2,null,'2024-01-19','2024-01-17','m','q'),(4,5,5,'2023-12-17','2023-12-18','q','o'),(5,3,2,'2023-12-15','2023-12-16','e','m'),(6,2,null,'2025-06-18','2025-02-18',null,'p'),(7,7,3,'2024-01-08','2024-02-18','j','f'),(8,8,9,'2024-01-31','2026-01-18','x','t'),(9,7,0,'2023-12-10','2025-02-18','p','b'),(10,2,4,'2023-12-12','2023-12-16','e','p'),(11,5,0,'2025-06-18','2025-06-18','n','v'),(12,0,4,'2024-01-31','2025-06-18','e',null),(13,1,null,'2023-12-18','2024-01-09','b','n'),(14,1,4,'2023-12-17','2023-12-12','n','y'),(15,3,6,'2024-01-09','2023-12-18','j','w'),(16,3,7,'2025-02-18','2024-01-09','a','c'),(17,4,0,'2024-02-18','2026-01-18','l','v'),(18,4,2,'2024-01-17','2023-12-14','o',null),(19,5,9,'2024-01-17','2024-02-18','k','n'),(20,3,3,'2023-12-14','2025-02-18',null,'q'),(21,9,8,'2023-12-19','2026-02-18','d','m'),(22,3,7,'2023-12-19','2025-02-18','k',null),(23,null,2,'2023-12-15','2027-01-09','x',null),(24,6,1,'2026-02-18','2026-01-18','b','i'),(25,6,5,'2025-02-18','2023-12-18','n','h'),(26,0,1,'2025-06-18','2023-12-12','c','q'),(27,3,8,'2026-01-18','2023-12-10','c','b'),(28,1,null,'2024-01-19','2027-01-09','b','l'),(29,0,6,'2023-12-11','2024-01-08','e',null),(30,2,7,'2025-06-18','2025-06-18','s','z'),(31,8,4,'2023-12-17','2026-02-18','h','t'),(32,7,5,'2023-12-10','2023-12-11','g',null),(33,3,2,'2023-12-10','2023-12-12','c','y'),(34,4,4,'2025-06-18','2024-02-18',null,'s'),(35,2,0,'2024-02-18','2023-12-18','e','m'),(36,6,0,'2023-12-10','2023-12-16',null,'t'),(37,8,6,'2023-12-14','2027-01-09',null,'z'),(38,1,9,'2023-12-10','2024-01-19','u','i'),(39,null,1,'2023-12-19','2025-06-18','g','p'),(40,7,5,'2024-01-31','2024-01-09','b',null),(41,2,7,'2024-02-18','2023-12-13','i','p'),(42,6,8,'2023-12-20','2024-01-17','q',null),(43,8,0,'2023-12-14','2023-12-11','a','g'),(44,0,null,'2025-06-18','2023-12-09','g','b'),(45,0,5,'2023-12-12','2024-01-19',null,null),(46,0,4,'2024-01-08','2023-12-14',null,'d'),(47,0,4,'2024-01-09','2027-01-09','d','a'),(48,null,6,'2026-01-18','2024-02-18','n','b'),(49,9,null,'2025-06-18','2023-12-14','r','e'),(50,1,3,'2024-01-19','2023-12-11','u','d'),(51,3,1,'2024-01-08','2023-12-20','h','b'),(52,0,null,'2023-12-10','2024-02-18','a','o'),(53,null,8,'2026-02-18','2023-12-10','k','o'),(54,2,7,'2025-06-18','2023-12-19','v','v'),(55,3,5,'2026-02-18','2023-12-11','x','o'),(56,8,5,'2024-01-17','2023-12-11',null,'i'),(57,0,8,'2025-02-18','2024-01-19','b','p'),(58,4,null,'2025-02-18','2025-02-18','e',null),(59,4,1,'2023-12-16','2024-01-17','z','c'),(60,3,8,'2023-12-09','2024-01-08','c','a'),(61,6,2,'2027-01-09','2023-12-15','w',null),(62,7,9,'2024-01-09','2024-01-17','w','o'),(63,6,0,'2025-02-18','2023-12-16','t',null),(64,0,null,'2024-02-18','2027-01-09','e','i'),(65,2,5,'2023-12-10','2023-12-16',null,null),(66,1,7,'2024-02-18','2023-12-20',null,'t'),(67,6,1,'2024-02-18','2025-02-18','v','s'),(68,7,6,'2023-12-12','2023-12-16','j','p'),(69,4,1,'2023-12-17','2023-12-18',null,'a'),(70,8,null,'2024-01-08','2023-12-15','l','b'),(71,7,3,'2023-12-16','2025-06-18',null,'c'),(72,6,4,'2023-12-15','2024-02-18','q','q'),(73,7,3,'2025-06-18','2024-01-31','j','z'),(74,2,null,'2024-01-17','2023-12-18','y','a'),(75,0,3,'2023-12-14','2027-01-09','c','k'),(76,7,3,'2027-01-16','2024-02-18','a','l'),(77,3,4,'2023-12-17','2024-01-19','j','n'),(78,0,null,'2024-01-31','2023-12-17','t',null),(79,8,8,'2027-01-09','2024-01-19','x','p'),(80,null,0,'2025-02-18','2024-01-17','s','o'),(81,null,3,'2023-12-12','2023-12-12',null,'k'),(82,7,1,'2023-12-15','2024-01-19','a','d'),(83,null,0,'2025-06-18','2023-12-18','v','y'),(84,null,9,'2025-02-18','2027-01-16','h','p'),(85,6,6,'2027-01-16','2023-12-10','j','d'),(86,4,5,'2027-01-16','2024-01-09','g','p'),(87,4,6,'2023-12-11','2026-01-18','k','n'),(88,4,8,'2023-12-12','2023-12-17','f','h'),(89,1,6,'2023-12-10','2023-12-11','w',null),(90,3,5,'2025-06-18','2023-12-11','s','x'),(91,null,6,'2023-12-11','2023-12-16','q','g'),(92,4,7,'2023-12-11','2023-12-16','r','c'),(93,3,9,'2026-01-18','2024-01-31','l','v'),(94,6,6,'2023-12-18','2023-12-15',null,'n'),(95,2,7,'2025-02-18','2024-02-18','m','w'),(96,9,null,'2026-02-18','2025-02-18','q','q'),(97,0,2,'2023-12-19','2027-01-16','t',null),(98,null,0,'2026-02-18','2024-02-18','k','t'),(99,4,1,'2025-06-18','2023-12-18','d','w'),(100,6,4,'2023-12-20','2023-12-18','f','h'),(101,1,null,'2026-02-18','2027-01-16','y',null),(102,null,null,'2023-12-12','2023-12-18','y','t'),(103,3,0,'2023-12-10','2023-12-09','n','u'),(104,8,9,'2023-12-18','2023-12-20','o','m'),(105,0,8,'2023-12-16','2024-01-19','v','k'),(106,null,0,'2023-12-16','2024-01-17','z','o'),(107,0,null,'2023-12-13','2024-02-18','q',null),(108,1,6,'2024-02-18','2026-01-18',null,'f'),(109,7,3,'2024-01-09','2027-01-09','h','c'),(110,7,null,'2023-12-12','2025-02-18','l','k'),(111,5,null,'2027-01-16','2023-12-10','o','f'),(112,6,8,'2023-12-16','2023-12-19','s',null),(113,6,9,'2023-12-20','2024-01-17',null,'c'),(114,3,8,'2025-06-18','2023-12-17','z','c'),(115,9,8,'2023-12-09','2023-12-10','k','d'),(116,null,5,'2024-01-08','2023-12-15','z','x'),(117,null,9,'2025-02-18','2023-12-20','j','x'),(118,null,4,'2024-02-18','2023-12-15','l','u'),(119,9,7,'2025-06-18','2023-12-10','n','b'),(120,6,null,'2025-06-18','2024-02-18','m','y'),(121,null,8,'2027-01-09','2023-12-17','d','i'),(122,0,7,'2025-06-18','2023-12-10','b','r'),(123,7,3,'2025-06-18','2024-02-18','a','b'),(124,3,0,'2026-02-18','2025-06-18','o','x'),(125,6,6,'2025-02-18','2023-12-15','c','m'),(126,8,0,'2024-02-18','2024-01-09','h','x'),(127,5,null,'2027-01-16','2023-12-11','i','j'),(128,3,2,'2024-01-08','2023-12-19','l','s'),(129,2,3,'2023-12-13','2027-01-16','j','x'),(130,2,1,'2023-12-15','2023-12-11','l',null),(131,null,null,'2024-02-18','2023-12-19','q','r'),(132,2,1,'2023-12-12','2024-01-31','p','q'),(133,0,3,'2024-01-17','2024-01-17','f','b'),(134,7,1,'2023-12-20','2027-01-16','y',null),(135,6,3,'2023-12-12','2023-12-10','v','l'),(136,6,5,'2023-12-09','2023-12-16','m','d'),(137,null,1,'2024-01-08','2023-12-13','b','q'),(138,4,2,'2024-01-09','2025-06-18',null,null),(139,1,5,'2024-02-18','2025-06-18','c',null),(140,9,9,'2024-02-18','2024-01-17','w',null),(141,2,5,'2023-12-13','2026-01-18','k','a'),(142,3,6,'2024-01-09','2023-12-18','p',null),(143,8,8,'2023-12-10','2025-06-18','i','g'),(144,3,9,'2023-12-20','2024-01-08','x','b'),(145,9,3,'2024-01-17','2024-01-17','h','t'),(146,0,8,'2027-01-16','2023-12-15','q','c'),(147,3,4,'2025-02-18','2023-12-10','d','j'),(148,null,0,'2023-12-13','2025-06-18','k','g'),(149,1,0,'2024-02-18','2023-12-14','f','k'),(150,5,0,'2023-12-20','2023-12-17','u','a'),(151,1,8,'2027-01-16','2024-01-08','k',null),(152,9,null,'2023-12-12','2024-01-17','q','v'),(153,null,0,'2023-12-09','2024-01-08',null,'b'),(154,8,9,'2023-12-19','2025-06-18','v','w'),(155,5,2,'2023-12-19','2024-01-19',null,'d'),(156,8,4,'2023-12-10','2025-02-18','n','s'),(157,4,2,'2023-12-15','2023-12-14','v','b'),(158,0,0,'2026-01-18','2025-02-18','v',null),(159,8,9,'2023-12-16','2023-12-14','i','w'),(160,null,9,'2025-06-18','2023-12-16','r','s'),(161,9,7,'2026-01-18','2023-12-19','d','i'),(162,4,7,'2025-02-18','2024-02-18','d','u'),(163,6,null,'2023-12-09','2024-01-09','l','k'),(164,6,2,'2023-12-14','2024-02-18','t','r'),(165,2,6,'2024-01-08','2024-01-17','w','g'),(166,null,7,'2023-12-12','2025-02-18','r',null),(167,3,5,'2024-02-18','2024-01-09','q','m'),(168,null,0,'2023-12-13','2024-01-17',null,'m'),(169,3,null,'2024-01-19','2024-01-17','f','e'),(170,5,4,'2027-01-16','2025-06-18','h','x'),(171,8,6,'2023-12-19','2024-02-18','j',null),(172,null,null,'2024-02-18','2025-06-18','y','l'),(173,0,3,'2023-12-16','2023-12-15','y','v'),(174,5,7,'2024-01-08','2025-06-18','h','k'),(175,null,6,'2023-12-16','2027-01-09','u','q'),(176,2,6,'2023-12-13','2024-01-09',null,'b'),(177,3,4,'2025-06-18','2023-12-20','y',null),(178,8,5,'2027-01-09','2023-12-18','d',null),(179,1,9,'2024-01-31','2023-12-16','f','s'),(180,null,6,'2023-12-10','2023-12-12','r','h'),(181,null,2,'2024-01-17','2024-01-31',null,'s'),(182,0,4,'2026-02-18','2024-02-18','w','y'),(183,0,8,'2026-02-18','2024-01-17','e','k'),(184,4,5,'2024-01-31','2026-02-18','w','c'),(185,9,7,'2024-01-31','2023-12-13','j','d'),(186,8,null,'2023-12-14','2023-12-14','e','b'),(187,6,3,'2024-01-09','2025-02-18','n','e'),(188,null,4,'2023-12-15','2023-12-16','n','y'),(189,2,9,'2023-12-16','2023-12-13',null,'p'),(190,null,1,'2024-01-09','2025-02-18','x','z'),(191,3,6,'2027-01-16','2026-01-18','t','w'),(192,2,0,'2027-01-16','2024-01-08','w','k'),(193,1,5,'2027-01-09','2024-02-18','h','u'),(194,7,1,'2023-12-17','2023-12-18','k','v'),(195,3,0,'2024-02-18','2024-01-31',null,'n'),(196,5,null,'2024-01-31','2025-06-18','a',null),(197,0,null,'2027-01-16','2024-01-17','p','v'),(198,6,null,'2023-12-16','2023-12-15','o','y'),(199,null,null,'2023-12-10','2027-01-09',null,'o'),(200,2,8,'2023-12-10','2024-01-08','e','p'),(201,null,2,'2026-02-18','2023-12-20',null,'d'),(202,3,1,'2023-12-17','2025-06-18','s','c'),(203,4,3,'2023-12-12','2023-12-13','f',null),(204,2,6,'2025-06-18','2023-12-15','r','c'),(205,6,8,'2024-02-18','2024-01-17','l','v'),(206,0,8,'2024-01-17','2023-12-09','l','z'),(207,null,1,'2023-12-19','2023-12-09','l','b'),(208,9,2,'2025-02-18','2025-02-18','k','i'),(209,null,8,'2025-02-18','2025-02-18','d','i'),(210,8,2,'2025-06-18','2027-01-09','s','x'),(211,5,0,'2024-02-18','2024-02-18','i',null),(212,null,8,'2023-12-15','2024-01-09',null,null),(213,1,8,'2024-01-19','2023-12-09','u','c'),(214,3,null,'2025-02-18','2024-02-18','j','d'),(215,2,5,'2027-01-09','2024-01-09','c','i'),(216,3,0,'2024-01-09','2023-12-15',null,'p'),(217,3,0,'2027-01-16','2023-12-13','q','e'),(218,2,9,'2025-06-18','2024-01-17','m','b'),(219,6,5,'2023-12-18','2024-02-18','d','o'),(220,0,8,'2024-02-18','2025-02-18','m','p'),(221,2,2,'2025-06-18','2027-01-16','z','o'),(222,1,5,'2025-06-18','2023-12-13','d','o'),(223,null,6,'2023-12-19','2025-06-18','i',null),(224,6,null,'2024-02-18','2024-02-18',null,'l'),(225,null,9,'2024-01-08','2023-12-14','y',null),(226,4,4,'2024-01-09','2023-12-09','u','l'),(227,9,5,'2025-06-18','2024-02-18','e','q'),(228,3,6,'2023-12-09','2023-12-19','y','l'),(229,3,9,'2023-12-12','2024-01-31','g',null),(230,5,4,'2023-12-13','2023-12-19','u','m'),(231,9,3,'2023-12-15','2025-02-18','y','c'),(232,7,5,'2023-12-10','2023-12-15','n','o'),(233,null,9,'2023-12-14','2023-12-15','n','p'),(234,8,null,'2023-12-09','2024-01-08','z','d'),(235,0,7,'2023-12-13','2023-12-19','t','s'),(236,null,7,'2025-06-18','2024-02-18','g',null),(237,2,1,'2023-12-09','2023-12-10','w','c'),(238,6,0,'2023-12-15','2025-02-18',null,'i'),(239,6,3,'2024-01-31','2023-12-13','x','s'),(240,1,0,'2024-02-18','2024-02-18',null,null),(241,7,4,'2026-01-18','2024-02-18','v','l'),(242,1,8,'2023-12-12','2027-01-09','w','i'),(243,0,5,'2027-01-16','2024-02-18','n','o'),(244,0,null,'2024-01-19','2023-12-18','c','z'),(245,4,null,'2023-12-18','2024-01-08','b',null),(246,4,5,'2027-01-09','2025-02-18','y','d'),(247,2,9,'2023-12-09','2027-01-16',null,'r'),(248,6,0,'2024-01-08','2023-12-15','z','p'),(249,4,8,'2025-02-18','2023-12-20','t',null),(250,6,6,'2023-12-18','2025-06-18','y','p'),(251,null,1,'2023-12-17','2023-12-19','s','t'),(252,4,8,'2024-01-08','2026-02-18','w','b'),(253,2,2,'2023-12-12','2023-12-18','t','c'),(254,5,4,'2023-12-10','2023-12-09','f','h'),(255,9,null,'2025-06-18','2024-02-18','a','a'),(256,3,4,'2027-01-16','2025-06-18','c','n'),(257,9,2,'2027-01-16','2027-01-16',null,'s'),(258,3,null,'2025-06-18','2023-12-12','l','r'),(259,8,null,'2023-12-20','2024-01-08','h','u'),(260,8,9,'2023-12-13','2024-01-19',null,'n'),(261,9,7,'2023-12-12','2025-02-18','t','j'),(262,6,null,'2023-12-17','2023-12-18','k','g'),(263,4,8,'2023-12-09','2023-12-18','h','k'),(264,7,null,'2026-01-18','2023-12-17','r','c'),(265,0,1,'2023-12-18','2024-02-18','u','j'),(266,7,6,'2023-12-15','2023-12-14','a','t'),(267,9,null,'2024-01-19','2026-02-18',null,null),(268,null,5,'2024-01-17','2024-01-31','w','t'),(269,8,4,'2025-02-18','2024-01-08','a','e'),(270,5,6,'2023-12-13','2024-02-18','e','n'),(271,null,5,'2027-01-16','2024-01-08','p','q'),(272,9,8,'2025-02-18','2024-02-18','r',null),(273,7,4,'2025-06-18','2026-01-18','s',null),(274,8,null,'2023-12-19','2024-01-17','x','e'),(275,2,5,'2024-02-18','2025-06-18','a',null),(276,0,0,'2024-02-18','2023-12-17','j','e'),(277,5,7,'2023-12-10','2023-12-17','y','i'),(278,5,0,'2025-02-18','2024-01-31','c','x'),(279,2,2,'2025-02-18','2024-01-09','u','s'),(280,6,4,'2023-12-20','2024-02-18','i','g'),(281,4,5,'2024-02-18','2023-12-12','d','a'),(282,8,6,'2023-12-13','2024-02-18','i','x'),(283,6,9,'2026-02-18','2023-12-12',null,'o'),(284,6,3,'2023-12-16','2026-02-18','u','d'),(285,3,2,'2023-12-10','2027-01-09','r','i'),(286,2,3,'2025-06-18','2024-01-17','d','l'),(287,0,4,'2026-02-18','2026-01-18','p','a'),(288,1,8,'2023-12-10','2026-01-18',null,'k'),(289,4,6,'2023-12-11','2025-02-18','o','s'),(290,5,3,'2023-12-13','2027-01-16','g','p'),(291,1,6,'2024-02-18','2023-12-11',null,'a'),(292,1,9,'2026-01-18','2025-06-18',null,'b'),(293,5,9,'2023-12-09','2023-12-09','l',null),(294,1,0,'2026-01-18','2024-01-31','o','u'),(295,null,2,'2024-01-19','2024-01-17','b','w'),(296,8,4,'2025-06-18','2023-12-15','v','q'),(297,3,4,'2023-12-16','2023-12-16','z',null),(298,5,2,'2023-12-11','2023-12-11','d','m'),(299,5,7,'2025-02-18','2024-01-08','p','h'),(300,1,9,'2024-02-18','2023-12-18','t','o'),(301,8,6,'2023-12-13','2025-06-18','k','x'),(302,null,3,'2023-12-11','2024-01-19','i','q'),(303,7,6,'2025-02-18','2024-01-08','j','s'),(304,5,5,'2023-12-15','2023-12-18','p','p'),(305,2,9,'2025-06-18','2024-01-08','v','y'),(306,0,1,'2024-01-19','2027-01-16',null,'a'),(307,8,2,'2027-01-09','2024-02-18','b','d'),(308,4,9,'2023-12-11','2023-12-17','z',null),(309,5,8,'2024-01-09','2024-01-08','d',null),(310,null,3,'2027-01-16','2023-12-16','k','o'),(311,2,6,'2025-06-18','2023-12-12','c','y'),(312,1,null,'2023-12-17','2023-12-16','e','o'),(313,3,0,'2023-12-14','2024-01-31','r','g'),(314,0,null,'2023-12-19','2024-01-19','m','j'),(315,4,8,'2023-12-18','2023-12-17','s','q'),(316,2,8,'2027-01-16','2024-02-18','o','b'),(317,1,null,'2023-12-10','2023-12-13','x','q'),(318,3,6,'2025-02-18','2024-02-18','p','t'),(319,5,7,'2023-12-12','2023-12-16',null,'z'),(320,6,1,'2024-02-18','2024-01-08','y','g'),(321,6,9,'2024-01-19','2026-01-18','g','i'),(322,8,2,'2024-02-18','2024-01-09','o','m'),(323,5,0,'2025-02-18','2023-12-20','p','v'),(324,2,9,'2023-12-17','2023-12-19','e','w'),(325,2,0,'2025-02-18','2023-12-12',null,'p'),(326,8,5,'2024-02-18','2023-12-17',null,'i'),(327,1,2,'2026-01-18','2023-12-20',null,'z'),(328,0,7,'2024-02-18','2024-02-18','h','w'),(329,8,null,'2025-02-18','2023-12-18','t','h'),(330,8,null,'2024-02-18','2023-12-17','c','m'),(331,4,null,'2023-12-11','2023-12-14',null,'s'),(332,4,4,'2023-12-17','2024-01-19','l','r'),(333,0,2,'2023-12-11','2025-02-18',null,'e'),(334,7,9,'2023-12-20','2025-02-18','l','z'),(335,7,9,'2023-12-19','2024-01-09','d','o'),(336,8,1,'2023-12-09','2023-12-10','x','j'),(337,9,0,'2024-01-09','2023-12-20','t','p'),(338,4,6,'2023-12-14','2024-01-08','z','z'),(339,null,5,'2023-12-09','2024-01-19',null,'d'),(340,0,8,'2023-12-20','2024-01-31','s','m'),(341,5,3,'2026-01-18','2023-12-10','i','w'),(342,3,null,'2024-01-08','2026-01-18','u',null),(343,null,2,'2024-01-17','2023-12-10','e','v'),(344,null,6,'2026-01-18','2023-12-11','m','d'),(345,1,null,'2023-12-12','2024-01-31','t','o'),(346,1,2,'2024-01-08','2023-12-15','y','i'),(347,3,4,'2027-01-16','2024-02-18','x','h'),(348,null,null,'2023-12-20','2023-12-11','v',null),(349,3,8,'2025-02-18','2024-01-31','b',null),(350,9,6,'2024-01-19','2024-02-18','o','w'),(351,6,2,'2023-12-14','2025-02-18','t','q'),(352,0,3,'2024-01-19','2024-02-18',null,'m'),(353,3,null,'2023-12-09','2025-02-18','d','z'),(354,9,2,'2023-12-18','2024-02-18','r','f'),(355,4,6,'2024-01-09','2024-01-17','z','t'),(356,1,1,'2023-12-13','2025-02-18','q','j'),(357,1,9,'2024-02-18','2026-02-18','w','z'),(358,1,8,'2024-01-17','2025-02-18','f','t'),(359,2,2,'2023-12-17','2023-12-12','c',null),(360,1,7,'2023-12-15','2023-12-18','v','i'),(361,9,2,'2026-02-18','2023-12-18','k','z'),(362,6,null,'2024-01-19','2023-12-19','v','x'),(363,null,3,'2024-02-18','2023-12-12','j','s'),(364,0,2,'2024-01-09','2023-12-18','l','m'),(365,3,7,'2027-01-16','2024-02-18',null,'x'),(366,4,5,'2025-06-18','2027-01-09','a','b'),(367,null,9,'2024-01-19','2026-01-18','z','g'),(368,null,1,'2025-06-18','2025-06-18','i','n'),(369,null,9,'2025-02-18','2026-02-18','h','n'),(370,null,3,'2024-02-18','2023-12-11','z','b'),(371,5,0,'2024-01-08','2023-12-19','j',null),(372,0,2,'2026-02-18','2023-12-12','r','m'),(373,null,0,'2024-02-18','2025-06-18',null,null),(374,6,2,'2024-01-17','2024-02-18','y','i'),(375,8,6,'2023-12-19','2023-12-13','h','e'),(376,7,2,'2023-12-10','2023-12-11','e','w'),(377,3,null,'2026-01-18','2024-01-08','z','r'),(378,4,3,'2024-01-31','2024-01-09','y','s'),(379,2,2,'2023-12-14','2023-12-16','d','t'),(380,8,1,'2024-01-19','2027-01-16','z','e'),(381,5,7,'2025-06-18','2024-01-17','y','p'),(382,0,4,'2023-12-15','2024-02-18','g','s'),(383,7,3,'2023-12-12','2023-12-15','w','c'),(384,4,null,'2023-12-18','2024-02-18','b','u'),(385,6,1,'2025-06-18','2024-02-18','c','s'),(386,null,4,'2023-12-18','2023-12-14','t','b'),(387,7,0,'2024-02-18','2023-12-15','l','q'),(388,null,8,'2025-02-18','2024-01-09','u','y'),(389,7,9,'2023-12-12','2024-01-17',null,'k'),(390,4,1,'2023-12-12','2025-02-18','b','u'),(391,1,null,'2026-02-18','2024-01-08','c','k'),(392,3,0,'2023-12-16','2025-06-18','r',null),(393,9,3,'2024-01-09','2027-01-16','p','t'),(394,3,2,'2024-02-18','2023-12-13',null,'u'),(395,9,6,'2023-12-14','2023-12-09','i','r'),(396,5,null,'2023-12-11','2023-12-17',null,'o'),(397,2,null,'2023-12-16','2023-12-14','b',null),(398,2,5,'2023-12-11','2024-01-17','d','h'),(399,2,5,'2023-12-19','2023-12-19','g','v'),(400,null,null,'2025-06-18','2023-12-15','i','o'),(401,7,4,'2023-12-13','2024-01-31','y','k'),(402,2,9,'2025-06-18','2023-12-17',null,'q'),(403,4,3,'2025-02-18','2027-01-09','s','w'),(404,5,9,'2023-12-20','2025-06-18','m','j'),(405,6,4,'2024-01-31','2023-12-14','u','e'),(406,4,5,'2023-12-09','2025-02-18','y','t'),(407,6,5,'2027-01-09','2024-02-18','n','n'),(408,6,8,'2024-01-08','2025-02-18','z','c'),(409,6,4,'2023-12-16','2025-06-18','t','z'),(410,6,2,'2026-02-18','2023-12-19','w',null),(411,8,null,'2023-12-17','2027-01-09','e','a'),(412,8,2,'2027-01-16','2023-12-13','b',null),(413,2,0,'2023-12-10','2023-12-17',null,'s'),(414,null,9,'2025-06-18','2024-02-18','k',null),(415,5,5,'2023-12-09','2023-12-11','g','g'),(416,9,5,'2025-02-18','2024-01-08','g','m'),(417,8,null,'2023-12-20','2024-01-19','g','b'),(418,3,6,'2024-01-31','2023-12-16',null,'f'),(419,4,0,'2024-01-31','2024-01-08','a',null),(420,4,1,'2025-06-18','2023-12-17','w','x'),(421,0,0,'2026-02-18','2023-12-17','i','n'),(422,0,9,'2023-12-16','2024-01-09','l','t'),(423,2,null,'2023-12-15','2024-01-09','b',null),(424,2,4,'2025-06-18','2023-12-20',null,'o'),(425,7,4,'2024-01-31','2025-02-18','w','u'),(426,null,null,'2023-12-18','2024-02-18','y','r'),(427,3,4,'2027-01-09','2023-12-10','p',null),(428,7,null,'2026-02-18','2024-02-18','p','a'),(429,null,3,'2023-12-19','2023-12-14','p','i'),(430,3,null,'2027-01-16','2023-12-15',null,'y'),(431,9,7,'2024-01-09','2023-12-18','e','r'),(432,6,2,'2023-12-18','2023-12-14','g','j'),(433,2,5,'2023-12-13','2025-02-18','y','b'),(434,2,3,'2025-02-18','2026-01-18','p','i'),(435,null,0,'2024-01-17','2024-01-19','g','h'),(436,9,6,'2024-01-17','2027-01-16','n','z'),(437,3,1,'2023-12-14','2023-12-17','o','r'),(438,6,1,'2024-02-18','2023-12-18','a','h'),(439,8,4,'2024-01-19','2024-01-31','a','s'),(440,9,0,'2026-02-18','2025-02-18','q','y'),(441,3,6,'2027-01-09','2025-06-18','r','f'),(442,6,1,'2023-12-14','2024-01-19','q','s'),(443,7,4,'2023-12-17','2024-02-18','q','t'),(444,1,null,'2025-06-18','2023-12-15','r','z'),(445,5,2,'2023-12-18','2024-01-17','v','q'),(446,2,0,'2023-12-16','2026-01-18',null,'v'),(447,null,6,'2027-01-09','2023-12-18','n','q'),(448,2,null,'2025-06-18','2024-02-18','e','t'),(449,null,2,'2026-02-18','2024-02-18','b','y'),(450,3,0,'2024-01-31','2024-02-18','e','n'),(451,6,4,'2024-01-08','2024-01-09','t','a'),(452,4,6,'2023-12-11','2024-01-08',null,'i'),(453,5,null,'2024-01-31','2023-12-19','t','f'),(454,2,0,'2024-02-18','2024-01-08','l','f'),(455,null,2,'2023-12-16','2023-12-17','j','o'),(456,2,5,'2023-12-13','2023-12-11','g','p'),(457,5,4,'2023-12-11','2023-12-15','y',null),(458,4,null,'2023-12-12','2023-12-11','r','r'),(459,null,0,'2025-02-18','2024-01-19','e',null),(460,7,5,'2025-02-18','2024-02-18','n','w'),(461,7,9,'2024-02-18','2027-01-09','f','v'),(462,0,9,'2025-02-18','2023-12-09','e','k'),(463,3,7,'2023-12-12','2023-12-18','b','i'),(464,6,8,'2023-12-10','2024-02-18','o','f'),(465,8,8,'2023-12-18','2024-01-08','k','h'),(466,8,5,'2023-12-18','2025-06-18','a','w'),(467,0,3,'2023-12-19','2026-02-18','i','i'),(468,4,5,'2027-01-16','2023-12-10',null,'m'),(469,3,7,'2023-12-17','2023-12-20','m','r'),(470,1,4,'2023-12-16','2023-12-14','p',null),(471,4,4,'2027-01-09','2027-01-16','l','t'),(472,2,null,'2024-01-08','2024-01-19','a','v'),(473,0,3,'2027-01-16','2023-12-12','b','j'),(474,7,9,'2024-02-18','2023-12-15','v','z'),(475,2,null,'2023-12-11','2025-02-18','v','d'),(476,8,2,'2023-12-11','2023-12-14','a','e'),(477,4,2,'2023-12-09','2025-02-18',null,'t'),(478,9,5,'2027-01-09','2023-12-11','j','e'),(479,8,4,'2024-01-31','2027-01-16','z','i'),(480,8,null,'2023-12-16','2024-02-18',null,'w'),(481,5,3,'2024-01-19','2024-02-18',null,'v'),(482,3,7,'2026-01-18','2024-01-19','t','k'),(483,9,9,'2023-12-13','2024-02-18','h','v'),(484,7,5,'2027-01-09','2023-12-19','r','w'),(485,9,4,'2023-12-20','2024-01-08',null,'c'),(486,8,null,'2023-12-10','2023-12-11','p','o'),(487,7,8,'2025-02-18','2025-02-18','a','b'),(488,4,0,'2023-12-18','2024-02-18','p',null),(489,4,8,'2023-12-15','2025-06-18',null,'u'),(490,5,9,'2024-02-18','2023-12-14','c','d'),(491,7,5,'2023-12-10','2023-12-14','t','h'),(492,6,7,'2023-12-20','2023-12-10','p','v'),(493,null,0,'2023-12-11','2024-02-18','g','a'),(494,8,5,'2024-01-09','2027-01-16','g','l'),(495,5,6,'2024-02-18','2025-02-18','p','e'),(496,3,9,'2024-01-17','2024-01-17','p','p'),(497,5,2,'2024-02-18','2025-02-18','n','b'),(498,4,9,'2024-01-17','2024-01-31','a','m'),(499,5,6,'2023-12-10','2025-02-18','p','k');
    """
    explain {
        sql """SELECT table1 . `col_int_undef_signed` AS field1 FROM test_partition_prune_or AS table1 WHERE 
         ( NOT (  ( table1 . `col_int_undef_signed` != table1 . `col_int_undef_signed` ) AND table1 . `col_date_undef_signed` <= '2025-06-18' ) AND 
         table1 . `col_date_undef_signed`  IN ( '2023-12-20' ) )  GROUP BY field1  ORDER BY field1 LIMIT 1000;
        """
        contains("partitions=5/8 (p0,p1,p2,p3,p5)")
    }
    qt_or_evaluate """SELECT table1 . `col_int_undef_signed` AS field1 FROM test_partition_prune_or AS table1 WHERE  
    ( NOT (  ( table1 . `col_int_undef_signed` != table1 . `col_int_undef_signed` ) AND table1 . `col_date_undef_signed` <= '2025-06-18' ) AND 
    table1 . `col_date_undef_signed`  IN ( '2023-12-20' ) )  GROUP BY field1  ORDER BY field1 LIMIT 1000;
    """
}